/**
 * Order数据模型模块
 */

//导入SQLite3模块
const sqlite3 = require("sqlite3").verbose();
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");

class OrderDB {
    /**
     * @constructor
     * @private
     */
    constructor() {
        this.dbFile = config.dbFile;
        this.instance = null;
        this.db = null;
    }

    /**
     * 创建OrderDB实例对象
     * @returns {OrderDB} OrderDB实例
     */
    static getInstance() {
        if (!this.instance) {
            this.instance = new OrderDB();
        }
        return this.instance;
    }

    /**
     * 连接数据库
     * @returns {Promise}
     */
    connect() {
        return new Promise((resolve, reject) => {
            this.db = new sqlite3.Database(this.dbFile, (err) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve("connect ok.");
                }
            });
        });
    }
    /**
     * 关闭数据库
     * @returns  {Promise}
     */
    close() {
        return new Promise((resolve, reject) => {
            this.db.close((err) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve("close ok.");
                }
            });
        });
    }

    /**
 * 新增订单
 * @param {Object} order 订单数据
 * @returns  {Promise}
 */
    add(order) {
        return new Promise((resolve, reject) => {
            let sql = `INSERT INTO orders (
            user_id,course_id,order_total,payment_method,course_source,order_time,transaction_id
            ) VALUES (?, ?, ?, ?, ?, ?, ?);`;
            let params = [
                order.userId,
                order.courseId,
                order.orderTotal,
                order.paymentMethod,
                order.courseSource,
                order.orderTime,
                order.transactionId,
            ];
            this.db.run(sql, params, function (err, result) {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(this.lastID); //插入的数据的自增ID
                }
            });
        });
    }

       /**
* 获取所有订单信息列表
* @param {Number} userId 用户ID
* @param {String} orderBy 排序字段
* @param {String} sort 排序方式
* @param {Number} limit 数量
* @param {Number} offset 开始
* @returns  {Promise}
*/
   findAll(orderBy = "orderTime", sort = "desc", limit = -1, offset = -1) {
    return new Promise((resolve, reject) => {
        let sql = `SELECT    
        o.id AS id,
        o.user_id AS userId,
        u.username AS userName,
        c.name AS courseName,
        t.username AS teacherName,
        c.subject AS courseSubject,
        c.category AS courseCategory,
        o.order_total AS orderTotal,
        o.course_source AS courseSource,
        o.order_time AS orderTime,
        o.transaction_id AS transactionId
        FROM orders o
    JOIN courses c ON o.course_id = c.id
    JOIN teachers t ON c.teacher_id = t.id
    JOIN users u ON o.user_id = u.id `;
        let params = [];
        if (limit === -1) {
            sql += `ORDER BY ${orderBy} ${sort}`;
        } else if (offset === -1) {
            sql += `ORDER BY ${orderBy} ${sort} LIMIT ?`;
            params.push(limit);
        } else {
            sql += `ORDER BY ${orderBy} ${sort} LIMIT ? OFFSET ?`;
            params.push(limit);
            params.push(offset);
        }
        this.db.all(sql, params, (err, result) => {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(result);
            }
        });
    });
}

    /**
 * 获取指定用户ID的订单信息列表
 * @param {Number} userId 用户ID
 * @param {String} orderBy 排序字段
 * @param {String} sort 排序方式
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns  {Promise}
 */
    findAllByUserId(userId, orderBy = "orderTime", sort = "desc", limit = -1, offset = -1) {
        return new Promise((resolve, reject) => {
            let sql = `SELECT    
            o.id AS id,
            o.user_id AS userId,
            u.username AS userName,
            c.name AS courseName,
            t.username AS teacherName,
            c.subject AS courseSubject,
            c.category AS courseCategory,
            o.order_total AS orderTotal,
            o.course_source AS courseSource,
            o.order_time AS orderTime,
            o.transaction_id AS transactionId
            FROM orders o
        JOIN courses c ON o.course_id = c.id
        JOIN teachers t ON c.teacher_id = t.id
        JOIN users u ON o.user_id = u.id
        WHERE o.user_id = ? `;
            let params = [userId];
            if (limit === -1) {
                sql += `ORDER BY ${orderBy} ${sort}`;
            } else if (offset === -1) {
                sql += `ORDER BY ${orderBy} ${sort} LIMIT ?`;
                params.push(limit);
            } else {
                sql += `ORDER BY ${orderBy} ${sort} LIMIT ? OFFSET ?`;
                params.push(limit);
                params.push(offset);
            }
            this.db.all(sql, params, (err, result) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(result);
                }
            });
        });
    }

    /**
 * 获取指定订单ID的订单信息
 * @param {Number} userId 用户ID
 * @returns  {Promise}
 */
find(orderId) {
    return new Promise((resolve, reject) => {
        let sql = `SELECT    
        o.id AS id,
        o.user_id AS userId,
        u.username AS userName,
        c.name AS courseName,
        t.username AS teacherName,
        c.subject AS courseSubject,
        c.category AS courseCategory,
        o.order_total AS orderTotal,
        o.course_source AS courseSource,
        o.order_time AS orderTime,
        o.transaction_id AS transactionId
        FROM orders o
    JOIN courses c ON o.course_id = c.id
    JOIN teachers t ON c.teacher_id = t.id
    JOIN users u ON o.user_id = u.id
    WHERE o.course_id = ? `;
        let params = [orderId];
        this.db.get(sql, params, (err, result) => {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(result);
            }
        });
    });
}

/**
* 获取指定课程ID的订单信息列表
* @param {Number} courseId 课程ID
* @param {String} orderBy 排序字段
* @param {String} sort 排序方式
* @param {Number} limit 数量
* @param {Number} offset 开始
* @returns  {Promise}
*/
findAllByCourseId(courseId, orderBy = "orderTime", sort = "desc", limit = -1, offset = -1) {
    return new Promise((resolve, reject) => {
        // 构造SQL查询语句
        let sql = `SELECT    
        o.id AS id,
        o.user_id AS userId,
        u.username AS userName,
        c.id AS courseId,
        c.name AS courseName,
        t.username AS teacherName,
        c.subject AS courseSubject,
        c.category AS courseCategory,
        o.order_total AS orderTotal,
        o.course_source AS courseSource,
        o.order_time AS orderTime,
        o.transaction_id AS transactionId
        FROM orders o
    JOIN courses c ON o.course_id = c.id
    JOIN teachers t ON c.teacher_id = t.id
    JOIN users u ON o.user_id = u.id
    WHERE c.id = ? `;
        let params = [courseId];// 初始化查询参数数组
        // 根据limit和offset的值构造不同的SQL查询语句
        if (limit === -1) {// 不限制查询结果数量，只按指定字段排序
            sql += `ORDER BY ${orderBy} ${sort}`;
        } else if (offset === -1) { // 限制查询结果数量，但不指定起始位置，只按指定字段排序
            sql += `ORDER BY ${orderBy} ${sort} LIMIT ?`;
            params.push(limit);
        } else {// 同时限制查询结果数量和起始位置，按指定字段排序
            sql += `ORDER BY ${orderBy} ${sort} LIMIT ? OFFSET ?`;
            params.push(limit);
            params.push(offset);
        }
        this.db.all(sql, params, (err, result) => { // 执行SQL查询
            if (err) {// 如果查询出错，则输出错误信息并拒绝Promise
                util.err(err);
                reject(err);
            } else { // 如果查询成功，则解析Promise并返回查询结果
                resolve(result);
            }
        });
    });
}

/**
 * 删除订单
 * @param {Number} orderId 订单ID
 * @returns  {Promise}
 */
remove(orderId) {
    return new Promise((resolve, reject) => {
        let sql = "DELETE FROM orders WHERE id = ?";
        let params = [orderId];
        this.db.run(sql, params, function (err, result) {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(this.changes); //影响的记录数
            }
        });
    });
}

/**
* 获取指定用户ID的订单数量
* @param {Number} userId 用户ID
* @returns  {Promise}
*/
getCount(userId) {
    return new Promise((resolve, reject) => {
         // 定义SQL查询语句，用于统计订单数量
        let sql = "SELECT count(1) AS total FROM orders WHERE user_id = ?";
        // 使用数据库查询方法，执行SQL查询
        let params = [userId];
        this.db.get(sql, params, function (err, result) {
            if (err) {// 如果查询过程中发生错误，则输出错误信息
                util.err(err);
                reject(err);
            } else {// 如果查询成功，则将查询结果中的total字段作为Promise的resolve值返回
                resolve(result.total);
            }
        });
    });
}

/**
* 获取所有的订单数量
* @returns  {Promise}
*/
getCountAll() {
    return new Promise((resolve, reject) => {
         // 定义SQL查询语句，用于统计订单数量
        let sql = "SELECT count(1) AS total FROM orders";
        // 使用数据库查询方法，执行SQL查询
        this.db.get(sql, function (err, result) {
            if (err) {// 如果查询过程中发生错误，则输出错误信息
                util.err(err);
                reject(err);
            } else {// 如果查询成功，则将查询结果中的total字段作为Promise的resolve值返回
                resolve(result);
            }
        });
    });
}

}

module.exports = OrderDB;